package com.dnjn.happiness.emo.mapper;


import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.dnjn.happiness.emo.api.domain.EmoAvgScoreWeekly;
import com.dnjn.happiness.emo.api.domain.WeeklyRankingBean;
import com.dnjn.happiness.emo.domain.vo.WeeklyComparisonVO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.List;

/**
 * 快乐值周均Mapper接口
 *
 * @author happiness
 * @date 2024-02-27
 */
public interface EmoAvgScoreWeeklyMapper extends BaseMapper<EmoAvgScoreWeekly> {

    @Select(" SELECT e.id, e.user_id,e.empno,e.remark, u.nick_name,e.ranking,ROUND(e.score_avg) scoreAvg, e.score_changed,u.avatar,u.sex FROM emo_avg_score_weekly AS e LEFT JOIN sys_user u USING(user_id) " +
            " LEFT JOIN sys_dept d ON u.dept_id = d.dept_id" +
            " WHERE e.week_of_year = #{weekOfYear} AND ( u.dept_id = #{deptId} OR u.dept_id IN ( SELECT t.dept_id FROM sys_dept t WHERE find_in_set(#{deptId}, ancestors ) )  ) ORDER BY ABS(score_changed) desc LIMIT 10")
    List<EmoAvgScoreWeekly> getLargeFluctuate(@Param("weekOfYear") String weekOfYear, @Param("deptId") Long deptId);

    @Select(" SELECT count(1) FROM emo_avg_score_weekly w " +
            " LEFT JOIN sys_user u USING(user_id) LEFT JOIN sys_dept d ON u.dept_id = d.dept_id " +
            " WHERE week_of_year = #{weekOfYear} AND score_avg > #{startValue} AND score_avg <= #{endValue} " +
            " AND w.deleted = 0 AND u.del_flag = '0' AND score_avg <= 600 AND ( u.dept_id = #{deptId} OR u.dept_id IN ( SELECT t.dept_id FROM sys_dept t WHERE find_in_set(#{deptId}, ancestors ) )  )")
    int getWeekRangePeople(@Param("weekOfYear") String weekOfYear, @Param("startValue") int startValue, @Param("endValue") int endValue, @Param("deptId") Long deptId);

    // 周均
//    @Select(" SELECT AVG(score_avg) FROM emo_avg_score_weekly WHERE week_of_year = #{weekOfYear} ")
//    BigDecimal getWeeklyAvg (@Param("weekOfYear") String weekOfYear);

    // 周均差
//    @Select(" SELECT ( ROUND(IFNULL((SELECT AVG(score_avg) FROM emo_avg_score_weekly WHERE week_of_year = YEARWEEK(NOW(),1)),0) " +
//            "- IFNULL((SELECT AVG(score_avg) FROM emo_avg_score_weekly WHERE week_of_year = YEARWEEK(NOW(),1)-1),0),0)) ")
    BigDecimal getWeeklyAvg(Long deptId);

    @Select(" SELECT ROUND(AVG(score_avg),2) scoreAvg,week_of_year,`week`,`year` FROM emo_avg_score_weekly w " +
            " LEFT JOIN sys_user u ON w.user_id = u.user_id" +
            " LEFT JOIN sys_dept d ON u.dept_id = d.dept_id  WHERE `year` = YEAR(NOW()) AND w.deleted = '0' AND u.del_flag = '0'  AND ( u.dept_id = #{deptId} OR u.dept_id IN ( SELECT t.dept_id FROM sys_dept t WHERE find_in_set(#{deptId}, ancestors ) )  )" +
            " GROUP BY week_of_year,`week`,`year` ORDER BY week_of_year ")
    List<WeeklyComparisonVO> getWeeklyComparison(Long deptId);


    @Select(" SELECT count(1) FROM emo_avg_score_weekly w LEFT JOIN sys_user u on w.user_id=u.user_id LEFT JOIN sys_dept d on u.dept_id =d.dept_id " +
            " WHERE week_of_year = #{weekOfYear} AND score_avg > #{startInterval} AND score_avg <= #{endInterval} AND w.deleted = '0' AND u.del_flag = '0' " +
            "AND ( u.dept_id = #{deptId} OR u.dept_id IN ( SELECT t.dept_id FROM sys_dept t WHERE find_in_set(  #{deptId}, ancestors ) ) ) ")
    Integer selectCountByWeekOfYear(@Param("startInterval") Integer startInterval, @Param("endInterval") Integer endInterval, @Param("weekOfYear") String weekOfYear, @Param("deptId") Long deptId);

    @Select(" SELECT e.nick_name,ROUND(e.score_avg,0) scoreAvg,e.score_changed,u.avatar,u.sex,u.user_id,u.empno FROM emo_avg_score_weekly e LEFT JOIN sys_user u USING(user_id) LEFT JOIN sys_dept d on u.dept_id=d.dept_id WHERE" +
            " e.deleted = 0 AND u.del_flag = '0'  AND ( u.dept_id = #{deptId} OR u.dept_id IN ( SELECT t.dept_id FROM sys_dept t WHERE find_in_set(  #{deptId} , ancestors ) ) )   AND e.week_of_year = #{weekOfYear} ORDER BY e.score_avg desc limit 0,10 ")
    List<WeeklyRankingBean> getTopTen(@Param("weekOfYear") String weekOfYear, @Param("deptId") Long deptId);

    @Select(" SELECT e.nick_name,ROUND(e.score_avg) scoreAvg,e.score_changed,u.avatar,u.sex,u.user_id,u.empno FROM emo_avg_score_weekly e LEFT JOIN sys_user u USING(user_id) LEFT JOIN sys_dept d on u.dept_id=d.dept_id WHERE" +
            " e.deleted = 0 AND u.del_flag = '0' AND ( u.dept_id =  #{deptId}  OR u.dept_id IN ( SELECT t.dept_id FROM sys_dept t WHERE find_in_set(  #{deptId} , ancestors ) ) ) AND e.week_of_year = #{weekOfYear} ORDER BY e.score_avg asc limit 0,10 ")
    List<WeeklyRankingBean> getLastTen(@Param("weekOfYear") String weekOfYear, @Param("deptId") Long deptId);

    List<EmoAvgScoreWeekly> getAgoList(@Param("weekOfYear") String weekOfYear, @Param("deptId") Long deptId);

    List<EmoAvgScoreWeekly> getExportList(@Param("dto") EmoAvgScoreWeekly emoAvgScoreWeekly);

    List<EmoAvgScoreWeekly> getAfterList(@Param("weekOfYear") String weekOfYear, @Param("deptId") Long deptId);
}
